﻿
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[bhp_NemoGroupRemove]
	@Id				INT
AS
BEGIN

	DECLARE
		@Sql	NVARCHAR(MAX),
		@SysName NVARCHAR(400)

	
		BEGIN TRAN
		
		SELECT @SysName = [name] FROM [sTree] WHERE id = @Id

		-- Usunięcie w bht_Groups
		DELETE FROM bht_Groups WHERE [Id] = @Id

		-- Usunięcie w sTree
		DELETE FROM [sTree] WHERE id = @Id

		
		-- Usunięcie tabeli T
		SET @Sql = 'DROP TABLE [dbo].[T' + CAST(@Id as NVARCHAR(10)) + ']'
		
		EXEC sp_executesql @Sql

		-- Usunięcie tabeli R
		SET @Sql = 'DROP TABLE [dbo].[R' + CAST(@Id as NVARCHAR(10)) + ']'
		
		-- Usunięcie widoku V
		SET @Sql = 'DROP VIEW [dbo].[' + @SysName + ']'
		EXEC sp_executesql @Sql
		
		
		COMMIT
	
END
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[bhp_NemoGroupGetAll]
AS
BEGIN

	SELECT
		t.id as [Id],
		g.Name,
		t.name as SystemName
	FROM
		sTree t
		LEFT JOIN bht_Groups g ON t.id = g.Id
	WHERE
		t.del = 0
		AND t.parent = 0
END
GO


